package com.cjack.frenchexcel.dao;

import com.cjack.frenchexcel.table.RechargeRecordTable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

/**
 * Created by root on 10/5/19
 */
public interface RechargeRecordDao extends JpaRepository<RechargeRecordTable, Long>, JpaSpecificationExecutor<RechargeRecordTable> {

    List< RechargeRecordTable> getByMerchantOrderNo( String merchantOrderNo);

    RechargeRecordTable getBySourceFromAndOtherId( String sourceFrom, Long otherId);

    @Transactional( timeout = 100)
    @Modifying
    @Query( value = "insert into fe_recharge_record(id,cashier_number,create_time,deal_status,deal_time,exchange_rate,memo,merchant_currency_type,merchant_name,merchant_number,merchant_order_amount,merchant_order_no,money_status,pay_currency_type,pay_money,pay_type,platform_order_no,refund_money,terminal_number,third_party_order_no,update_time,deal_time_french) VALUES(:#{#data.id},:#{#data.cashierNumber},:#{#data.createTime},:#{#data.dealStatus},:#{#data.dealTime},:#{#data.exchangeRate},:#{#data.memo},:#{#data.merchantCurrencyType},:#{#data.merchantName},:#{#data.merchantNumber},:#{#data.merchantOrderAmount},:#{#data.merchantOrderNo},:#{#data.moneyStatus},:#{#data.payCurrencyType},:#{#data.payMoney},:#{#data.payType},:#{#data.platformOrderNo},:#{#data.refundMoney},:#{#data.terminalNumber},:#{#data.thirdPartyOrderNo},:#{#data.updateTime},:#{#data.dealTimeFrench})", nativeQuery = true)
    void restoreData( @Param("data") RechargeRecordTable table);

    /**
     * 清除所有数据
     */
    @Transactional( timeout = 100)
    @Modifying
    @Query( value = " delete from fe_recharge_record where 1=1", nativeQuery = true)
    void clearAll();

    /**
     * 查询按天统计 第一步 的到账数据列表
     * @return
     */
    @Query( value = " select DATE_FORMAT(deal_time,\"%Y-%m-%d\") deal_time,ROUND(sum(merchant_order_amount),2)merchant_order_amount from fe_recharge_record where find_in_set(money_status, ?1) and source_from !='RECUPERSO_TRIGGER' GROUP BY DATE_FORMAT(deal_time,\"%Y-%m-%d\") order by deal_time asc limit 15", nativeQuery = true)
    List< Object[]> listByDayOne( String moneyStatus);

    /**
     * 查询按天统计的 第二步 的到账数据列表
     * @return
     */
    @Query( value = " select DATE_FORMAT(deal_time,\"%Y-%m-%d\") deal_time,ROUND(sum(merchant_order_amount),2)merchant_order_amount from fe_recharge_record where find_in_set(money_status, ?1) and source_from !='RECUPERSO_TRIGGER' GROUP BY DATE_FORMAT(deal_time,\"%Y-%m-%d\") order by deal_time asc limit 15", nativeQuery = true)
    List< Object[]> listByDayTwo( String moneyStatus);

    /**
     * 查询按天统计的第三步 的到账数据列表
     * @return
     */
    @Query( value = " select DATE_FORMAT(deal_time,\"%Y-%m-%d\") deal_time,ROUND(sum(merchant_order_amount),2)merchant_order_amount from fe_recharge_record where find_in_set(money_status, ?1) and source_from !='RECUPERSO_TRIGGER' GROUP BY DATE_FORMAT(deal_time,\"%Y-%m-%d\") order by deal_time asc limit 15", nativeQuery = true)
    List< Object[]> listByDayThree( String moneyStatus);

    /**
     * 保存修改到账状态 第一步操作对应的时间-，都通过deal_time字段来操作
     */
    @Transactional( timeout = 100)
    @Modifying
    @Query( value = " update fe_recharge_record set update_money_status_one=now(),money_status=?2 where DATE_FORMAT(deal_time,\"%Y-%m-%d\") = ?1", nativeQuery = true)
    void setUpdateMoneyStatusDateOne( String day, String moneyStatus);
    /**
     * 保存修改到账状态 第二步操作对应的时间 都通过deal_time字段来操作
     */
    @Transactional( timeout = 100)
    @Modifying
    @Query( value = " update fe_recharge_record set update_money_status_two=now(),money_status=?2 where DATE_FORMAT(deal_time,\"%Y-%m-%d\") = ?1", nativeQuery = true)
    void setUpdateMoneyStatusDateTwo( String day, String moneyStatus);
    /**
     * 保存修改到账状态 第三步操作对应的时间  都通过deal_time字段来操作
     */
    @Transactional( timeout = 100)
    @Modifying
    @Query( value = " update fe_recharge_record set update_money_status_three=now(),money_status=?2 where DATE_FORMAT(deal_time,\"%Y-%m-%d\") = ?1", nativeQuery = true)
    void setUpdateMoneyStatusDateThree( String day, String moneyStatus);

    /**
     * 统计该员工所有已经到账的的充值总额
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(merchant_order_amount), 2), 0)pay_money from fe_recharge_record where import_user_id=?1 and money_status=6", nativeQuery = true)
    Double sumByMoneyStatusAndAdminUserId( Long adminUserId);

    /**
     * 统计所有已经到账的的充值总额
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(merchant_order_amount), 2), 0)pay_money from fe_recharge_record where money_status=6", nativeQuery = true)
    Double sumByMoneyStatus();

    /**
     * 统计该员工所有已经的充值总额
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(merchant_order_amount), 2), 0)pay_money from fe_recharge_record where import_user_id=?1 ", nativeQuery = true)
    Double sumByAdminUserId( Long adminUserId);

    /**
     * 统计时间段内该员工所有的充值总额
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(merchant_order_amount), 2), 0)pay_money from fe_recharge_record where import_user_id=?1 and deal_time_french > ?2 and deal_time_french < ?3", nativeQuery = true)
    Double sumByAdminUserIdAndDealTimeFrench( Long adminUserId, String startDealTime, String endDealTime);
}
